import openpyxl
from openpyxl import load_workbook

from ConfigFile.Varconfig import testData_path


class ExcelOperate:

    def __init__(self):
        self.workbook = None
        self.sheet = None

    def load_workbook(self,filename):
        '''
        加载文件
        :param filename:
        :return:
        '''
        try:
            self.workbook = load_workbook(filename)
            # print(self.workbook.sheetnames)
        except Exception as e:
            print(e)

    def get_sheet_by_name(self,sheetname):
        '''
        获取sheet页
        :param sheetname:
        :return:
        '''
        try:
            self.sheet = self.workbook[sheetname]
        except Exception as e:
            print(e)

    def get_rows_nums(self):
        '''
        获取最大行数
        :return:
        '''
        return self.sheet.max_row

    def get_col_nums(self):
        '''
        获取最大列数
        :return:
        '''
        return self.sheet.max_column

    def get_row_values(self,row):
        '''
        获取某一行的值
        :param row:
        :return:
        '''
        cloums = self.sheet.max_column
        row_data = []
        for i in range(1,cloums+1):
            cell_values = self.sheet.cell(row=row,column=i).value
            row_data.append(cell_values)
        return row_data

    def get_cell_values(self,row,column):
        '''
        获取某个单元格的值
        :param row:
        :param cloumn:
        :return:
        '''
        cell_values = self.sheet.cell(row=row,column=column).value
        return cell_values

    def write_cell(self,row,column,value):
        '''
        写入指定单元格内容
        :param row:
        :param column:
        :param value:
        :return:
        '''
        try:
            self.sheet.cell(row=row,column=column,value=value)
            self.workbook.save(testData_path)
        except Exception as e:
            print(e)


if __name__ == '__main__':
    ex = ExcelOperate()
    ex.load_workbook(testData_path)
    ex.get_sheet_by_name('Sheet1')
    print(ex.get_rows_nums())
    print(ex.get_col_nums())
    print(ex.get_row_values(1))
    print(ex.get_cell_values(2,2))
    ex.write_cell(2,7,'pass')
